/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.examples.enhancedads.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import com.google.api.adwords.starterkit.db.structures.CampaignStructure;
import com.google.api.adwords.starterkit.utils.DumpExceptions;

/**
 * TextAd2CatId maps the relationship between the categories and their
 * local ids in the database.
 * 
 * @author rodrigo.f.vale@gmail.com
 */
public class TextAd2CatId {
  private Connection conn;
  private PreparedStatement update;
  private static final Logger log = Logger.getLogger("StarterKit");
  
  public TextAd2CatId(Connection conn) {
    this.conn = conn;
    Statement stat;
    try {
      stat = conn.createStatement();
      stat.executeUpdate(
          "CREATE TABLE IF NOT EXISTS TextAd2CatId "
        + "  ( "
        + "     LocalId         LONG, "
        + "     CategoryId      STRING, "
        + "     PRIMARY KEY (LocalId)"
        + "  ); ");

      stat.executeUpdate(
          "CREATE INDEX IF NOT EXISTS TextAd2CatIdCatIdIndex " 
        + "on TextAd2CatId (CategoryId);");
      
      update = conn.prepareStatement(
          "REPLACE INTO TextAd2CatId (LocalId, CategoryId) " 
        + "VALUES (?, ?);");

    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void clear() {
    Statement stat;
    try {
      stat = conn.createStatement();
      stat.executeUpdate("DELETE FROM TextAd2CatId");            
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void saveBatch(int localId, String catId) {
    try {
      update.setInt(1, localId);
      update.setString(2, catId);
      update.addBatch();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void executeBatch() {
    log.debug("Executing batch at TextAd2CatId");
    try {
      update.executeBatch();           
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  
  public String getCategoryId(int localId) {
    PreparedStatement prep = null;
    String categoryId = "";
    try {
      prep = conn.prepareStatement(
            "SELECT CategoryId FROM TextAd2CatId WHERE localId = ?");
      prep.setInt(1, localId);
      ResultSet rs = prep.executeQuery();
      if (rs.next()) {
        categoryId = rs.getString(1);
        rs.close();
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
    return categoryId;
  }
}